package com.test;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import com.other.generater.JDBCUtils;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by baiguantao on 2018/1/18.
 */
public class CG {
    public static void invest06CG() throws Exception {
        //标题
        List<ExcelExportEntity> entityList = new ArrayList<ExcelExportEntity>();
        //内容
        List<Map<String,Object>> dataResult = new ArrayList<Map<String,Object>>();
        /*商户号	平台用户ID	用户角色类型*/
        entityList.add(new ExcelExportEntity("商户号", "table1", 15));
        entityList.add(new ExcelExportEntity("平台用户ID", "table2", 25));
        entityList.add(new ExcelExportEntity("用户角色类型", "table3", 35));
        String url2 = "jdbc:mysql://ruiqiantong.mysql.rds.aliyuncs.com:3306/rqt_v2?"
                + "user=rqtread&password=wjmdsrqbao@2017&useUnicode=true&characterEncoding=UTF8";
        JDBCUtils.getConnection(null,url2);

        ResultSet resultSet = JDBCUtils.getSql("SELECT\n" +
                "\t\tr.rqt_user_id uid\n" +
                "\tFROM\n" +
                "\t\tcg_register r\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND r.biz_type = '06'");
        String pid="M20000030480";
        while (resultSet.next()){
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("table1", pid);
            map.put("table2", resultSet.getString("uid"));
            if ("5294e51b32fc40d3a160a288506a31c9".equals(resultSet.getString("uid").toString())) {
                map.put("table3", "02");
            }else{
                map.put("table3", "01");
            }
            dataResult.add(map);
        }
        ExportParams params=new ExportParams("代授权用户列表", "代授权用户列表Sheet");
        Workbook workbook= ExcelExportUtil.exportExcel(params,entityList,dataResult);
        try {
            workbook.write(new FileOutputStream("C:\\Users\\temp\\Desktop\\invest06CG.xls"));
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void invest_06() throws Exception {
        String url2 = "jdbc:mysql://ruiqiantong.mysql.rds.aliyuncs.com:3306/rqt_v2?"
                + "user=rqtread&password=wjmdsrqbao@2017&useUnicode=true&characterEncoding=UTF8";
        JDBCUtils.getConnection(null,url2);

        ResultSet resultSet = JDBCUtils.getSql("SELECT\n" +
                "\t\tr.rqt_user_id uid,r.id rid\n" +
                "\tFROM\n" +
                "\t\tcg_register r\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND r.biz_type = '06'");
        String pid="M20000030480";
        StringBuilder sb=new StringBuilder();
        while (resultSet.next()){
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("table1", pid);
            map.put("table2", resultSet.getString("uid"));
            String rid=resultSet.getString("rid");
            String biz_type="01";
            String biz_type_name="投资用户";
            if ("5294e51b32fc40d3a160a288506a31c9".equals(resultSet.getString("uid").toString())) {
                biz_type="02";
                biz_type_name="借贷用户";
            }
            sb.append("update cg_register set biz_type='"+biz_type+"',biz_type_name='"+biz_type_name+"' where id='"+rid+"'; \n");

        }

        System.out.println(sb.toString());
        save("invest_06.sql",sb.toString());
    }

    /**
     * 由存管代为授权的缴费和还款【借款人】
     * @throws Exception
     */
    public static void borrowerGrantXls() throws Exception {
        //标题
        List<ExcelExportEntity> entityList = new ArrayList<ExcelExportEntity>();
        //内容
        List<Map<String,Object>> dataResult = new ArrayList<Map<String,Object>>();
        /*商户号	平台用户ID	用户角色类型*/
        entityList.add(new ExcelExportEntity("P2P用户ID", "table1", 15));
        entityList.add(new ExcelExportEntity("授权类型（枚举：INVEST,REPAY,SHARE_PAYMENT）", "table2", 25));
        entityList.add(new ExcelExportEntity("授权期限（枚举：1年、3年、5年；不填-无期限", "table3", 35));
        entityList.add(new ExcelExportEntity("授权金额（10万/笔、50万/笔、100万/笔）", "table4", 45));
        String url2 = "jdbc:mysql://ruiqiantong.mysql.rds.aliyuncs.com:3306/rqt_v2?"
                + "user=rqtread&password=wjmdsrqbao@2017&useUnicode=true&characterEncoding=UTF8";
        JDBCUtils.getConnection(null,url2);

        ResultSet resultSet = JDBCUtils.getSql("SELECT\n" +
                "\t\tl.borrower_id uid\n" +
                "\tFROM\n" +
                "\t\tloan l\n" +
                "\tGROUP BY\n" +
                "\t\tl.borrower_id");
        String pid="M20000030480";
        while (resultSet.next()){
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("table1", resultSet.getString("uid"));
            map.put("table2", "REPAY,SHARE_PAYMENT");
            map.put("table3", "5");
            map.put("table4", "100");
            dataResult.add(map);
        }
        ExportParams params=new ExportParams("平台用户授权改造统计模版（注意：对于授权免密投资的老用户不重新授权，对于年限/金额无限制）", "代授权用户列表Sheet");
        Workbook workbook= ExcelExportUtil.exportExcel(params,entityList,dataResult);
        try {
            workbook.write(new FileOutputStream("C:\\Users\\temp\\Desktop\\borrowerGrantXls.xls"));
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * 由存管代为授权的缴费和还款【借款人】补单SQL
     * @param args
     * @throws Exception
     */
    public static void borrowerIds(String[] args) throws Exception {
        String url2 = "jdbc:mysql://ruiqiantong.mysql.rds.aliyuncs.com:3306/rqt_v2?"
                + "user=rqtread&password=wjmdsrqbao@2017&useUnicode=true&characterEncoding=UTF8";
        JDBCUtils.getConnection(null,url2);

        ResultSet resultSet = JDBCUtils.getSql("SELECT\n" +
                "\t\tl.borrower_id uid\n" +
                "\tFROM\n" +
                "\t\tloan l\n" +
                "\tGROUP BY\n" +
                "\t\tl.borrower_id");
        String pid="M20000030480";
        StringBuilder sb=new StringBuilder();
        while (resultSet.next()){
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("table1", resultSet.getString("uid"));
            map.put("table2", "REPAY,SHARE_PAYMENT");
            sb.append("insert into `cg_user_grant_list` " +
                    "(`user_id`,`grant_list`,`status`,`limit_amount`,`lose_time`,`create_time`,`remarks`) values " +
                    "('"+resultSet.getString("uid")+"','REPAY','1','1000000','2023-01-22 01:00:00','2018-01-22 01:00:00','统一处理迁移数据'); \n");
          sb.append("insert into `cg_user_grant_list` " +
                    "(`user_id`,`grant_list`,`status`,`limit_amount`,`lose_time`,`create_time`,`remarks`) values " +
                    "('"+resultSet.getString("uid")+"','SHARE_PAYMENT','1','1000000','2023-01-22 01:00:00','2018-01-22 01:00:00','统一处理迁移数据'); \n");

        }
        System.out.println(sb.toString());
        save("borrowerIds.sql",sb.toString());
    }

    public static  void save(String path,String content){
        try {
            FileOutputStream fis=new FileOutputStream(new File("C:\\Users\\temp\\Desktop\\"+path));
            fis.write(content.getBytes());
            fis.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception {
       // invest06CG();
      /*  System.out.println("#########################华丽的分割线#########################################");
        //borrowerGrantXls();
        System.out.println("#########################华丽的分割线#########################################");
        //sql语句
        invest_06();
        System.out.println("#########################华丽的分割线#########################################");
        borrowerIds(null);*/
        try {
            long start=System.currentTimeMillis();
//            String responseBodyn = HttpsUtils.sendRequestTem(getP());
            URL url2 = new URL("http://fsgw.hkmdev.firstpay.com/phoenixFS-fsgw/gateway");
            HttpURLConnection conn = (HttpURLConnection) url2.openConnection();
            conn.setReadTimeout(500);
            conn.setConnectTimeout(500);
            conn.setRequestMethod("GET");
            conn.disconnect();
            long end=System.currentTimeMillis();
            System.out.println(end-start);
        }catch (Exception e){
            System.out.println("哎呀，失败了"+e.getClass());
        }
    }
}
